﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Slja.DBUtility;

using System.Collections;
using Slja.Model;

namespace Slja.DAL
{
    public class ImportData 
    {
        public ImportData()
        {
           // DbHelperSQL.connectionString = DbHelperSQL.connectionString;
        }
       public int ExecuteSQL(string strSQL)
       {
           int n=  DbHelperSQL.ExecuteSql(strSQL);
           return n;
       }
       public void ImportDataBySQLAndParams(Hashtable hash)
       {
           DbHelperSQL.ExecuteSqlTran(hash);
       }
       public int SaveComparableTableColumns(string TableName, List<string> ColNames)
       {
           if (string.IsNullOrEmpty(TableName) || ColNames.Count == 0)
           {
               throw new ArgumentNullException("参数不能为空");
           }
           string delSQL = "delete from f_ComparableTableColumns where TableName='" + TableName + "';";
           StringBuilder addSqL = new StringBuilder("insert into f_ComparableTableColumns(TableName,FieldName)");
           for (int i = 0; i < ColNames.Count; i++)
           {
               addSqL.AppendFormat(" select '{0}','{1}' union all", TableName, ColNames[i]);
           }
           addSqL.Length -= 9;//除最后的union all

           List<string> sqllist = new List<string>();
           sqllist.Add(delSQL);
           sqllist.Add(addSqL.ToString());
           return DbHelperSQL.ExecuteSqlTran(sqllist);           

       }
       public List<TableCol> GetComparableTableColumnsModel(string TableName)
       {
           DataTable dt = GetComparableTable(TableName);
           List<TableCol> Cols = new List<TableCol>();
           TableCol col;
           for (int i = 0; i < dt.Rows.Count; i++)
           {
               col = new TableCol();
               col.ColumnName = dt.Rows[i]["FieldName"].ToString();
               col.TableName = dt.Rows[i]["TableName"].ToString();
               Cols.Add(col);
           }
           return Cols;
       }
       public List<string> GetComparableTableColumns(string TableName)
       {
           DataTable dt = GetComparableTable(TableName);
           List<string> Cols = new List<string>();
           for (int i = 0; i < dt.Rows.Count; i++)
           {
               Cols.Add(dt.Rows[i]["FieldName"].ToString());
           }
           return Cols;
       }
       private DataTable GetComparableTable(string TableName)
       {
           string sql = "";
           if (TableName == "")
           {
               sql = "select FieldName,TableName from f_ComparableTableColumns";
           }
           else
           {
               sql = "select FieldName,TableName from f_ComparableTableColumns where TableName='" + TableName + "'";
           }

           DataSet ds = DbHelperSQL.Query(sql);
           return ds.Tables[0];
       }
       public DataTable GetTableDataByName(string tableName)
       {
           string sql = "select * from " + tableName ;
           return DbHelperSQL.Query(sql).Tables[0];
       }
       public Boolean DbTableExists(string tableName)
       {
          return  DbHelperSQL.TabExists(tableName);
       }
       public DataTable GetJoinedData(DateTime dt1,DateTime dt2)
       {
           SqlParameter[] paras = new SqlParameter[2];
           (paras[0] = new SqlParameter("@dt1", SqlDbType.DateTime)).Value = dt1;
           (paras[1] = new SqlParameter("@dt2", SqlDbType.DateTime)).Value = dt2;
          DataSet ds = DbHelperSQL.RunProcedure("sp_getJoinedAccountData", paras, "JoinedData");
          return ds.Tables[0];
       }
       public DataTable GetJoinedData(DateTime dt1)
       {
           SqlParameter[] paras = new SqlParameter[1];
           (paras[0] = new SqlParameter("@dt1", SqlDbType.DateTime)).Value = dt1;
           //(paras[1] = new SqlParameter("@dt2", SqlDbType.DateTime)).Value = dt2;
           DataSet ds = DbHelperSQL.RunProcedure("sp_getJoinedAccountData", paras, "JoinedData");
           return ds.Tables[0];
       }
        /// <summary>
       /// 调用sql函数，取表的列名(依规定的设置返回)
       /// 参数为'',则取全部3个表的
        /// </summary>
       /// <param name="tableName">如：dbo.f_Receivable</param>
       /// <returns>返回字段为tablename,columnname的DataTable</returns>
       public DataTable GetTableColumns(string tableName)
       {
           string sql = "select tablename,columnname,rank from dbo.fn_SetTableColumns('" + tableName + "')";//调用sql函数，取几个表的列名
           DataSet ds = DbHelperSQL.Query(sql);
           return ds.Tables[0];
       }
       
    }
}
